﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class TraCuuSachDAO
    {
        private static string ChuoiKetNoi = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=DB/DATA.mdb";

        protected static OleDbConnection MoKetNoi()
        {
            OleDbConnection KetNoi = new OleDbConnection(ChuoiKetNoi);
            KetNoi.Open();
            return KetNoi;
        }

        public static List<TraCuuSachDTO> TraCuuSach(TraCuuSachDTO dk)
        {
            int i;
            List<TraCuuSachDTO> ListBook = new List<TraCuuSachDTO>();
            OleDbConnection KetNoi = null;
            List<String> ChuoiDieuKien = new List<string>();
            List<OleDbParameter> DSThamSo = new List<OleDbParameter>();
            OleDbParameter ThamSo;

            if (string.Compare(dk.Tensach, "", true) != 0)
            {
                ChuoiDieuKien.Add("TuaSach = @TenSach");
                ThamSo = new OleDbParameter("@TenSach", OleDbType.VarChar);
                ThamSo.Value = dk.Tensach;
                DSThamSo.Add(ThamSo);
            }

            if (string.Compare(dk.Theloai, "Tất cả", true) != 0)
            {
                ChuoiDieuKien.Add("TheLoai = @TheLoai");
                ThamSo = new OleDbParameter("@TheLoai", OleDbType.VarChar);
                ThamSo.Value = dk.Theloai;
                DSThamSo.Add(ThamSo);
            }

            if (string.Compare(dk.Tacgia, "", true) != 0)
            {
                ChuoiDieuKien.Add("TacGia = @TacGia");
                ThamSo = new OleDbParameter("@TacGia", OleDbType.VarChar);
                ThamSo.Value = dk.Tacgia;
                DSThamSo.Add(ThamSo);
            }

            KetNoi = MoKetNoi();

            string CauTruyVan = "SELECT * FROM Sach";

            for (i = 0; i < ChuoiDieuKien.Count; i++)
            {
                if (i == 0)
                {
                    CauTruyVan += " WHERE ";
                    CauTruyVan += ChuoiDieuKien[i];
                }
                else
                {
                    CauTruyVan += " AND ";
                    CauTruyVan += ChuoiDieuKien[i];
                }
            }

            OleDbCommand Lenh = new OleDbCommand(CauTruyVan, KetNoi);

            for (i = 0; i < ChuoiDieuKien.Count; i++)
                Lenh.Parameters.Add(DSThamSo[i]);

            OleDbDataReader data = Lenh.ExecuteReader();
            while (data.Read())
            {
                TraCuuSachDTO s = new TraCuuSachDTO();

                if (!data.IsDBNull(0))
                    s.Masach = data.GetString(0);
                if (!data.IsDBNull(1))
                    s.Tensach = data.GetString(1);
                if (!data.IsDBNull(2))
                    s.Theloai = data.GetString(2);
                if (!data.IsDBNull(3))
                    s.Tacgia = data.GetString(3);
                if (!data.IsDBNull(8))
                    s.SL = data.GetInt16(8);

                ListBook.Add(s);
            }

            return ListBook;
        }
    }
}
